from utils.sql_utils import SQLManager
from flask import request
from utils.id_utils import IdUtils
from utils.time_utils import TimeUtils
from app.service.note_service import NoteService
from service.img_service import ImgService

class SearchCategoryService:

    def get_category_list(request):
        # current = request.get_json().get('current')
        sql = """
            SELECT 
                rnc.note_id AS category_note_id,
                rnc.name AS category_name,
                rnc.description AS category_description,
                rnc.unique_id AS category_id
            FROM rb_note_category rnc
            WHERE is_deleted = '0';
        """
        raw_category_list = SQLManager().get_list(sql)

        # 用于存储已存在的 category_name
        seen = set()

        # 去重后的类别列表
        unique_category_list = []
        
        for category in raw_category_list:
            category_name = category.get('category_name')
            if category_name and category_name not in seen:
                unique_category_list.append(category)
                seen.add(category_name)
        
        return unique_category_list
    
    def get_category_list_by_name(request):
        current = request.get_json().get('current')
        category_name = request.get_json().get('category_name')
        sql = """
            SELECT 
                rnc.unique_id AS note_id,
                rnc.title AS note_title,
                rnc.content AS note_content,
                rnc.image_url AS note_image_url,
                rnc.like_count AS note_like_count,
                rnc.created_at AS note_created_at,
                rnc.modify_time AS note_modify_time,
                
                rnmi.location AS map_location,
                rnmi.longitude AS map_longitude,
                rnmi.latitude AS map_latitude,
                
                rau.unique_id AS user_id,
                rau.admin_user_name AS user_name,
                rau.avatar_url AS user_avatar_url,
        
                rnca.name AS category_name,
                rnca.unique_id AS category_id,

                COUNT(rncl.unique_id) AS collection_count

            FROM 
                rb_note_content rnc
            JOIN rb_note_map_info rnmi ON rnc.map_info_id = rnmi.unique_id
            JOIN rb_admin_user rau ON rnc.user_id = rau.unique_id
            JOIN rb_note_category rnca ON rnc.unique_id = rnca.note_id
            LEFT JOIN rb_note_collections rncl ON rnc.unique_id = rncl.note_id
            WHERE 
                rnca.name = %s
            AND 
                rnca.is_deleted = '0'
            GROUP BY 
                rnc.unique_id, rnc.title, rnc.content, rnc.image_url, rnc.like_count, rnc.created_at, 
                rnc.modify_time, rnmi.location, rnmi.longitude, rnmi.latitude, rau.unique_id, 
                rau.admin_user_name, rau.avatar_url,rnca.name,rnca.unique_id;
        """
        if category_name is None:
            note_list = SQLManager().get_list_by_page(sql,current=int(current))
            for note in note_list:
                note_id = note['note_id']
                note['slider_images'] = []
                
                # 获取当前笔记的轮播图信息
                slider_images = ImgService.query_note_slider_by_note_id(note_id, None, None)
                for img in slider_images:
                    note['slider_images'].append(img)
            return {'note_info':note_list}
        params = (category_name,)
        note_list = SQLManager().get_list_by_page(sql,args=params,current=int(current))
        for note in note_list:
            note_id = note['note_id']
            note['slider_images'] = []
            
            # 获取当前笔记的轮播图信息
            slider_images = ImgService.query_note_slider_by_note_id(note_id, None, None)
            for img in slider_images:
                note['slider_images'].append(img)
        return {'note_info':note_list}
    
    def insert_category_note(note_id,category_name,category_description):
        sql = """
            INSERT 
            INTO rb_note_category 
                (
                    unique_id, note_id, name,
                    description, created_at,
                    modify_time, is_deleted
                ) 
            VALUES 
                (%s, %s, %s, %s, %s, %s, %s);
        """
        params = (IdUtils.generate_id(),note_id,category_name,
                  category_description,
                  TimeUtils.get_current_time_stamp(),
                  TimeUtils.get_current_time_stamp(),
                  0)
        return SQLManager().insert(sql,params)
    
    def insert_category(category_name):
        sql = """
            INSERT 
            INTO rb_note_category 
                (
                    unique_id, note_id, name,
                    description, created_at,
                    modify_time, is_deleted
                ) 
            VALUES 
                (%s, %s, %s, %s, %s, %s, %s);
        """
        params = (IdUtils.generate_id(),
                  "",
                  category_name,
                  "",
                  TimeUtils.get_current_time_stamp(),
                  TimeUtils.get_current_time_stamp(),
                  0)
        
        return SQLManager().insert(sql,params)
    
    def change_category_statu(category_name,category_statu):
        sql = """
            UPDATE 
                rb_note_category 
            SET 
                is_deleted = %s
            WHERE 
                name = %s;
        """
        params = (category_statu,category_name,)
        
        return SQLManager().insert(sql,params)
    
    def get_category_statu_list(request):
        # current = request.get_json().get('current')
        sql = """
            SELECT 
                rnc.note_id AS category_note_id,
                rnc.name AS category_name,
                rnc.description AS category_description,
                rnc.unique_id AS category_id,
                rnc.is_deleted AS category_statu
            FROM rb_note_category rnc
        """
        raw_category_list = SQLManager().get_list(sql)

        # 用于存储已存在的 category_name
        seen = set()

        # 去重后的类别列表
        unique_category_list = []
        
        for category in raw_category_list:
            category_name = category.get('category_name')
            if category_name and category_name not in seen:
                unique_category_list.append(category)
                seen.add(category_name)
        
        return unique_category_list
    
    def update_category(category_name,category_id):
        sql = """
            UPDATE 
                rb_note_category 
            SET 
                name = %s
            WHERE 
                unique_id = %s;
        """
        params = (category_name,category_id,)
        
        return SQLManager().insert(sql,params)
